<?php

/*
* Class teia\webdev\php\core\MySqlLink
* Core class for connecting to and querying a MySql database
*
* @author Bernardo Quibiana
* @date created: 20111005
*
* @date modified: 20111005
* @modified: Bernardo Quibiana
*/

// namespace declaration: Core Abstract Data Types
namespace teia\webdev\php\core;

class MySqlLink {
	protected $link;	// mysql link identifier
	protected $host;	// database host
	protected $user; 	// database user
	protected $pwd; 	// password for the specified user
	protected $db;		// name of the database
	protected $result;	// the last result
	
	static private $_singleton = null;
	
	/**
	 * Instantiate the object and attemps to esblablish a connection with MySQL server.
	 * @param string $host the address of MySQL server (default: "localhost:3306")
	 * @param string $user a user registered on the server (default: none, use configuration's default)
	 * @param string $pw the password for the specified user (default: none)
	 * @param string $db name of a database to use as soon as the connection is established (default: none)
	 * @throws Exception Throws exception on connection failure
	 */
	public function __construct($host="localhost:3306", $user="", $pw="", $db="") {
		$this->host = $host;
		$this->user = $user;
		$this->pwd = $pw;
		$this->db = $db;
		
		// attempt to connect
		try {
			$this->connect();
		} catch (Exception $e) {
			//echo 'Exception in MySqlLink: ' . $e->getMessage();
			throw $e;
		}
	}

	/**
	 * 
	 */
	public function __destruct(){
		@mysql_close($this->link); // suppress eventual errors
	}

	/**
	 * Enter description here ...
	 * @return multitype:string 
	 */
	public function __sleep(){
		return array('host', 'user', 'pwd', 'db');
	}

	/**
	 * Enter description here ...
	 */
	public function __wakeup(){
		$this->connect();
	}

	public static function link_factory($reuse=TRUE){
		if ($reuse) {
			if (is_null(self::$_singleton)) {
				require_once 'inc_sec.inc';
				self::$_singleton = new MySqlLink($sec_db_host, $sec_db_user, $sec_db_pwd, $sec_db_name);
			}
			return self::$_singleton;
		} else {
			require_once 'inc_sec.inc';
			return new MySqlLink($sec_db_host, $sec_db_user, $sec_db_pwd, $sec_db_name);
		}
		
	}
	
	public static function get_error($link=NULL) {
		if ($link) {
			return mysql_error($link) . ' [' . mysql_errno($link) . ']';
		}
		return mysql_error() . ' [' . mysql_errno() . ']';
	}
	
	/**
	 * Connect to MySQL server
	 * @throws Exception Throws exception on connection failure
	 */
	protected function connect(){
		// Attempt to connect
		$this->link = mysql_connect($this->host, $this->user, $this->pwd);
		$err = mysql_error($this->link);
		if ($err) {
			$code = mysql_errno($this->link);
			throw new \Exception($err, $code);
		}
		elseif (!$this->link) {
			throw new \Exception('MySqlLink::connect - Could not connect: Unknown error', 999);
		}			
		// Connection successful, try to select db
		$result = $this->select_db($this->db);
		return $result;
	}

	public function display_info() {
		echo "Link to MySQL database <br />";
		echo "Host: $this->host <br />";
		echo "User: $this->user <br />";
		echo "Database: $this->db <br />";
		echo "Status: $this->link";	
	}
	
	public function escape_str($value) {
		return mysql_real_escape_string($value, $this->link);
	}
	
	public function escape_array($array) {
		$escaped = array();
		foreach ($array as $key => $value) {
			if (is_array($value)) {
				$escaped[$key] = $this->escape_array($value);
			} else {
				$escaped[$key] = $this->escape_str($value);
			}
		}
		return $escaped;
	}
	
	public function prepare_insert_data($array) {
		$keys = FunctionPool::array_to_str(array_keys($array), ',');
		$escaped_values = $this->escape_array($array);
		$values = FunctionPool::array_to_str($escaped_values, ',', "'");
	
		return "($keys) values($values)";
	}
	
	public function prepare_where_clause($conds, $limit=0) {
		$conds = $this->escape_array($conds);
		$conds_str = (is_null($conds) || empty($conds))? '' : 'WHERE ' . FunctionPool::assoc_to_str($conds, ' AND ', "'", '=');
		$limit_str = $limit > 0 ? "LIMIT $limit" : '';
		
		return "$conds_str $limit_str";
	}
	
	/*
	 * Send a simple MySQL select query: "SELECT cols FROM table [WHERE conds]" (For more complex selects use query($sql))
	 * @param string $cols string with the list of columns to select
	 * @param string $table the table on which to perform the select
	 * @param string $condition the conditions for the where clause
	 * @return resource returns a resource on success or false on error
	 */
	public function select($db_tb, $cols=NULL, $conds=NULL, $limit=0, $op='=', $rel='AND'){
		$_conds = $this->escape_array($conds);
		$sql = SqlUtil::query_select($cols, $db_tb, $_conds, $limit, $op, $rel);
		return $this->query($sql);
	}
	
	public function search($db_tb, $cols, $conds, $strict_op=FALSE, $strict_rel=FALSE, $limit=0){
		// SELECT cols FROM table [WHERE conds]
		$op = $strict_op ? '=' : 'LIKE' ;
		$rel = $strict_rel ? ' AND ' : ' OR ' ;
		$_conds = $this->escape_array($conds);
		$sql = SqlUtil::query_select($cols, $db_tb, $_conds, $limit, $op, $rel);
		
		return $this->query($sql);
	}
	
	public function power_search($db_tb, $cols, $conjuction, $disjunction, $op_conj='=', $op_disj='=', $limit=0) {
		$col_str = is_null($cols) ? '*' : FunctionPool::array_to_str($cols, ', ', '');
		$where = SqlUtil::where_clause($conjuction, $disjunction, $op_conj, $op_disj);
		$limit_str = $limit > 0 ? "LIMIT $limit" : '';
		$sql = "SELECT $col_str FROM $db_tb $where $limit_str";
		
		return $this->query($sql);
	}
	
	public function insert($db_tb, $data, $conds=null,  $op='=', $rel='AND') {
		$_data =  $this->escape_array($data);
		$sql = SqlUtil::query_insert($db_tb, $_data, $conds, $op, $rel);
		// execute the query
		$result = $this->query($query);
		if ($result) {
			return mysql_affected_rows($this->link);
		} 
		return 0;
	}

	/*
	*
	mysql_query($query, $link_identifier)
	Send a MySQL query
	
	Parameters:
	query string
	A SQL query
	
	The query string should not end with a semicolon. Data inside the query should be properly escaped.
	
	link_identifier resource[optional]
	Returns:
	resource For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query returns a resource on success, or false on error.
	For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query returns true on success or false on error.
	
	The returned result resource should be passed to mysql_fetch_array, and other functions for dealing with result tables, to access the returned data.
	
	Use mysql_num_rows to find out how many rows were returned for a SELECT statement or mysql_affected_rows to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.
	
	mysql_query will also fail and return false if the user does not have permission to access the table(s) referenced by the query.
	
	link
	http://www.php.net/manual/en/function.mysql-query.php
	*/
	/**
	 * Send a MySql query (wrapper for mysql_query($query, $link_identifier)); 
	 * <http://www.php.net/manual/en/function.mysql-query.php>
	 * @param string $sql the SQL query string to send
	 * @throws Exception Throws exception on empty query
	 * @return resource see mysql_query
	 */
	public function query($sql) {
		if ($sql) {
			$this->result = mysql_query($sql, $this->link);
			$err = mysql_error($this->link);
			if ($err) {
				$code = mysql_errno($this->link);
				throw new \Exception($err, $code);
			}
			return $this->result;
		} else {
			throw new \InvalidArgumentException('Invalid SQL query', 999);
		}
	}

	
	/**
	 * Send a MySql query and verify the number of rows in the result set
	 * @param string $sql the SQL query string to send
	 * @param int $min_num_rows minimum number of rows expected in the result set
	 * @param int $max_num_rows max number of rows expected in the result set, defaults to 1
	 * @return boolean return true if the number of rows in the result set of the query executed satisfy the constraint (min, max), otherwise false
	 */
	public function verication_query($sql, $min_num_rows=1, $max_num_rows=1) {
		$result = $this->query($sql);
		//echo "<!-- DEBUG::MySqlLink: query result: $result\t" . mysql_error() . " - " . mysql_errno() . "-->\n";
		if (is_resource($result)) {
			$count = mysql_num_rows($result);
			//echo "<!-- DEBUG::MySqlLink: rows: $count\n -->\n";
			return ($min_num_rows <= $count && $count <= $max_num_rows);
		} elseif (is_int($result)) {
			return ($min_num_rows <= $result && $result <= $max_num_rows);
		}
		// expected boolean
		return $result;
	}
	/**
	 * Attempt to return the number of rows returned for the last SELECT statement or the number of rows affected by the last DELETE, INSERT, REPLACE, or UPDATE statement
	 * @return number the number of rows returned for a SELECT statement or affected by DELETE, INSERT, REPLACE, or UPDATE statement
	 */
	public function row_count() {
		$count = mysql_num_rows($this->result);
		if ($count) {
			return $count;	
		}
		$count = mysql_affected_rows($this->link);
		
		return $count;
	}
	
	public function get_err_mgs() {
		return mysql_error($this->link);
	}
	
	public function get_err_num() {
		return mysql_errno($this->link);
	}
	
	public function get_err() {
		return mysql_error($this->link) . ' [' . mysql_errno($this->link) . ']';
	}
	
	/**
	 * Create an empty database with the specified name.
	 * @param string $db name of the new database.
	 * @return resource 
	 */
	public function create_db($db){
		$sql = "CREATE DATABASE $db";
		return $this->query($sql);
	}
	
	/**
	 * Select the speficied database;
	 * @param string $db name of the database to use;
	 * @return boolean returns true on success or false on failure.
	 */
	public function select_db($db){
		$this->db = $db;
		return mysql_select_db($this->db, $this->link);
	}
}

?>